﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using Sinacor.OMM.Logging;
using System.Diagnostics;

namespace Sinacor.OMM.ConnectionManager.DataBase
{
    public class OracleDatabase : IDataBase
    {
        Sinacor.Infra.Service.Data.OracleDataBase oracleDataBase = null;
        private int _companyId;
        DbConnection _connection;
        DbCommand dbCommand;
        DbTransaction _transaction;

        public OracleDatabase()
        {
        }

        public bool Connected
        {
            get
            {
                return (_connection != null && (_connection.State == System.Data.ConnectionState.Open || _connection.State == System.Data.ConnectionState.Fetching || _connection.State == System.Data.ConnectionState.Executing));
            }
        }

        public bool Connect()
        {
            bool ret = false;
            try
            {
                if (oracleDataBase == null)
                {
                    if (CompanyId == 0)
                    {
                        throw new ArgumentOutOfRangeException("CompanyId precisa ser maior ou igual a 1");
                    }
                    oracleDataBase = new Sinacor.Infra.Service.Data.OracleDataBase(CompanyId);
                    _connection = oracleDataBase.CreateConnection();
                    _connection.Open();
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao tentar realizar a conexão no banco [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
                //TODO: LOG
                ret = false;
            }
            return ret;
        }

        public void Disconnect()
        {
            try
            {
                if (_connection != null)
                {
                    _connection.Close();
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao desconectar o banco [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
                //TODO: LOG
            }
        }

        public void Close()
        {
            try
            {

                if (oracleDataBase != null)
                    oracleDataBase.Dispose();

                _connection.Close();
                _connection.Dispose();
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao tentar fechar a conexão com o banco [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
            }
            _connection = null;
            oracleDataBase = null;
        }

        public int CompanyId
        {
            get
            {
                return _companyId;
            }
            set
            {
                _companyId = value;
            }
        }

        public bool ExecuteNonQuery(string command)
        {
            bool ret = false;

            try
            {
                if (Connected)
                {
                    dbCommand = oracleDataBase.GetSqlStringCommand(command);
                    ret = !(oracleDataBase.ExecuteNonQuery(dbCommand) == -1);
                }
                else
                {
                    throw new Exception("Erro na conexão com o Banco de Dados");
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao executar ExecuteNonQuery [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
                ret = false;
                throw ex;
            }
            return ret;
        }

        public bool ExecuteNonQuery(DbTransaction transaction, string command)
        {
            bool ret = false;

            try
            {
                if (Connected)
                {
                    dbCommand = oracleDataBase.GetSqlStringCommand(command);
                    ret = !(oracleDataBase.ExecuteNonQuery(dbCommand, transaction) == -1);
                }
                else
                {
                    throw new Exception("Erro na conexão com o Banco de Dados");
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao executar ExecuteNonQuery [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
                ret = false;
                throw ex;
            }
            return ret;
        }


        public bool ExecuteNonQuerySP(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            bool ret = false;

            try
            {
                if (Connected)
                {
                    dbCommand = oracleDataBase.GetStoredProcCommand(storedProcedureName, parameterValues);
                    ret = !(oracleDataBase.ExecuteNonQuery(dbCommand, transaction) == -1);
                }
                else
                {
                    throw new Exception("Erro na conexão com o Banco de Dados");
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao executar ExecuteNonQuery [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
                ret = false;
                throw ex;
            }
            return ret;
        }


        public System.Data.IDataReader ExecuteReader(string command)
        {
            try
            {
                if (Connected)
                {
                    dbCommand = oracleDataBase.GetSqlStringCommand(command);

                    return oracleDataBase.ExecuteReader(dbCommand);
                }
                else
                {
                    throw new Exception("Erro na conexão com o Banco de Dados");
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao executar ExecuteReader [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
            }

            return null;
        }

        public object ExecuteScalar(string command)
        {
            try
            {
                if (Connected)
                {
                    dbCommand = oracleDataBase.GetSqlStringCommand(command);

                    return oracleDataBase.ExecuteScalar(dbCommand);
                }
                else
                {
                    throw new Exception("Erro na conexão com o Banco de Dados");
                }
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry(LogSources.MSMQConnectionManager, "Erro ao executar ExecuteScalar [" + ex.Message + " " + ex.StackTrace + "]", EventLogEntryType.Warning);
            }
            return null;
        }

        public DbTransaction BeginTransaction()
        {
            //dbCommand.Transaction.Connection.BeginTransaction();
            _transaction = _connection.BeginTransaction();
            return _transaction;
        }

        public void RollBack()
        {
            //dbCommand.Transaction.Rollback();
            _transaction.Rollback();
        }

        public void Commit()
        {
            //dbCommand.Transaction.Commit();
            _transaction.Commit();
        }
    }
}
